var express = require('express'); //引入express模块
var router = express.Router(); //引入express路由
const { sendCodeMail } = require('../utils/emit.js')
const { verify } = require('../utils/index.js')
const xlsx = require("node-xlsx"); //引入xlsx模块
const fs = require("fs");
const path = require("path")
// 引入数据模块
const conn = require('./db/conn')

/* GET home page. 打开的首页 */
router.get('/', function (req, res, next) {
  res.render('index', { title: 'Express' });
});

//跨域
router.all('*', (req, res, next) => {
  // 设置响应头,解决跨域
  res.header('Access-Control-Allow-Origin', '*'); //允许所有域名访问
  res.header('Access-Control-Allow-Headers', '*'); //允许访问的请求头
  res.header('Access-Control-Allow-Methods', '*'); //允许访问的请求方法
  next() //放行 
})


// 验证函数 是否必填
// function verify(obj) {
//   let flag = false //标杆
//   let arr = []  //要返回没有填的数据
//   Object.keys(obj).forEach(key => {  //Object.keys遍历对象返回的是键名的数组
//     if (obj[key]) {
//       flag = true  //验证数据通过
//     } else {        //验证数据不通过 返回没有填写的数据
//       arr.push({ key })
//       flag = false
//     }
//   })
//   return { flag, arr }
// }

/*  
接口
router 路由对象,可以接收前端请求,也可以响应数据给前端
   router.请求方式('/请求地址',(req.res)=>{
    req(request):请求对象,负责接收前端参数的
        get 请求的参数 req.query.参数名
        post 请求的参数 req.body.参数名
     res(response):响应对象,负责响应数据给前端
     res.send("发送给前端的数据")
   })
*/
//引入随机id
const { generateUserId } = require('../utils/index')

//1.注册接口
router.post('/student/reg', (req, res) => {
  let { name, password, sex, age, tel } = req.body
  let r = verify({ name, password, sex, age, tel })
  // 返回错误信息
  if (!r.flag) {
    res.send({ code: 5001, success: false, msg: '参数错误' })
    console.log(r.arr);
    return
  }
  //验证密码
  let reg = /^[a-zA-Z0-9_-]{8,16}$/
  if (!reg.test(password)) {
    res.send({ code: 5002, success: false, msg: '密码格式错误' })
    return
  }
  //验证手机号
  let reg2 = /^1[3456789]\d{9}$/
  if (!reg2.test(tel)) {
    res.send({ code: 5003, success: false, msg: '手机号格式错误' })
    return
  }

  let sno = '2024-426-' + Math.floor(Math.random() * 10000) + Date.now() //学号随机数
  let sql = `insert into student(name,password,sex,age,tel,sno,sid) values('${name}','${password}','${sex}','${age}','${tel}','${sno}','${generateUserId()}')`
  conn.query(sql, (error, results) => {
    if (error) throw error; //如果报错，抛出错误
    console.log(results);
    if (results.affectedRows > 0) {
      res.send({ code: 0, success: true, msg: '注册成功' })
    } else {
      res.send({ code: 1, success: false, msg: '注册失败' })
    }
  });
})

const jwt = require('jsonwebtoken');
const { log } = require('console');
const SECRET_KEY = 'login2021'
//2.登录接口
router.post('/student/login', (req, res) => {
  let { account, password } = req.body
  // 参数验证返回错误信息
  let r = verify({ account, password })
  if (!r.flag) {
    res.send({ code: 5001, success: false, msg: '参数错误' })
    console.log(r.arr);
    return
  }
  const token = jwt.sign(
    { user: { account: `${account}`, password: `${password}` } },
    SECRET_KEY,
    { expiresIn: '3h' }
  )
  // console.log('🚀 → token', token)
  let sql = `select * from student where tel='${account}' and password='${password}'`
  conn.query(sql, (error, results) => {
    if (error) throw error;
    if (results.length > 0) {
      res.send({ code: 0, success: true, msg: '登录成功', data: { token: 'Bearer ' + token, results } })
    } else {
      res.send({ code: 1, success: false, msg: '登录失败' })
    }
  })
})

//3.获取学生列表接口
router.get('/student/list', (req, res) => {
  let userId = req.query.userId
  let r = verify({ userId })
  if (!r.flag) {
    res.send({ code: 5001, success: false, msg: '参数错误' })
    console.log(r.arr);
    return
  }
  let sql = `select * from student where sid='${userId}'`
  conn.query(sql, (error, results) => {
    if (error) throw error;
    if (results.length > 0) {
      let sql = `select * from student`
      conn.query(sql, (error, results) => {
        if (error) throw error;
        res.send({ code: 0, success: true, msg: '获取数据成功', data: results })
      })
    } else {
      res.send({ code: 3, success: false, msg: '您没有权限查看数据' })
    }
  })


})
//4.查看详情接口
router.get('/student/info', (req, res) => {
  let userId = req.query.userId
  let r = verify({ userId })
  if (!r.flag) {
    res.send({ code: 5001, success: false, msg: '参数错误' })
    console.log(r.arr);
    return
  }
  let sql = `select * from student where sid='${userId}'`
  conn.query(sql, (error, results) => {
    if (error) throw error;
    if (results.length > 0) {
      res.send({ code: 0, success: true, msg: '获取数据成功', data: results[0] })

    } else {
      res.send({ code: 3, success: false, msg: '您没有权限查看数据' })
    }
  })

})
//5.编辑接口
router.post('/student/edit', (req, res) => {
  let { name, password, sex, age, tel, userId } = req.body
  let r = verify({ userId })
  if (!r.flag) {
    res.send({ code: 5001, success: false, msg: '参数错误' })
    console.log(r.arr);
    return
  }
  let setClauses = [];
  if (name !== undefined) setClauses.push(`name='${name}'`);
  if (password !== undefined) setClauses.push(`password='${password}'`);
  if (sex !== undefined) setClauses.push(`sex='${sex}'`);
  if (age !== undefined) setClauses.push(`age='${age}'`);
  if (tel !== undefined) setClauses.push(`tel='${tel}'`);
  // 如果没有要更新的字段，直接返回错误
  if (setClauses.length === 0) {
    res.send({ code: 5002, success: false, msg: '没有要修改的数据' });
    return;
  }
  const setClause = setClauses.join(',');
  const sql = `UPDATE student SET ${setClause} WHERE id='${userId}'`;

  conn.query(sql, (error, results) => {
    if (error) throw error;
    console.log(results);
    if (results.affectedRows > 0) {
      res.send({ code: 0, success: true, msg: '修改成功' })
    } else {
      res.send({ code: 1, success: true, msg: '修改失败' })
    }
  })
})
//6.删除学生接口
router.get('/student/delete', (req, res) => {
  let userId = req.query.userId
  let r = verify({ userId })
  if (!r.flag) {
    res.send({ code: 5001, success: false, msg: '参数错误' })
    console.log(r.arr);
    return
  }
  let sql = `delete from student where id='${userId}'`
  conn.query(sql, (error, results) => {
    if (error) throw error;
    if (results.affectedRows > 0) {
      res.send({ code: 0, success: true, msg: '删除成功' })
    } else {
      res.send({ code: 1, success: true, msg: '删除失败' })
    }
  })

})

//发邮箱验证码
router.post('/student/sendCode', async (req, res) => {
  // util.promisify是Node.js内置的util模块的一个方法，用于将传统的Node.js风格的异步函数（即使用回调作为最后一个参数的函数）转换为返回Promise的函数
  const util = require('util')
  conn.query = util.promisify(conn.query);
  let { emitName } = req.body
  let r = verify({ emitName })
  if (!r.flag) {
    res.send({ code: 5001, success: false, msg: '参数错误' })
    console.log(r.arr);
    return
  }
  const emailRegex = /^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/;
  if (!emailRegex.test(emitName)) {
    res.send({ code: 5002, success: false, msg: '邮箱格式有误,请重新输入' })
    console.log(emitName);
    return
  }

  // 调用发送邮件函数接收验证
  let emitCode = sendCodeMail(emitName)
  res.send({ code: 0, success: true, msg: '发送成功' })
  // 1.先查询表中是否有这个数据,如果有先把旧的数据删除了 确保数据的唯一性
  const sendSql = `select * from emailcode where email='${emitName}'`
  // 把查询结果异步转化为同步的代码
  const sendSqlResult = await conn.query(sendSql);
  if (sendSqlResult.length > 0) {
    const deleteSql = `DELETE FROM emailcode WHERE email='${emitName}'`
    const deleteSqlResult = await conn.query(deleteSql);
    console.log('删除成功....', deleteSqlResult);
  }




  // 2.然后把新的验证码存到数据库
  let sql = `insert into emailcode(email,email_code) values('${emitName}','${emitCode}')`
  conn.query(sql, (error, results) => {
    if (error) throw error; //如果报错，抛出错误
    // console.log(results); //成功返回的数据
    // 3.验证码存储成功后给一个定时器5分钟删除验证码
    if (results.affectedRows > 0) {
      console.log('添加成功....', results);
      setTimeout(() => {
        const sql = `DELETE FROM emailcode WHERE email='${emitName}' AND email_code='${emitCode}'`;
        conn.query(sql, (error, results) => {
          if (error) throw error

        });
      }, 1000 * 60);
    }
  });

})

//验证验证码是否正确emitCode
router.post('/student/verifyCode', (req, res) => {
  let { mailNum, mailCode } = req.body

  const sql = `select * from  emailcode where email='${mailNum}' and email_code='${mailCode}'`
  conn.query(sql, (error, results) => {
    if (error) throw error;
    if (results.length > 0) {
      const sql = `DELETE FROM emailcode WHERE email='${mailNum}' AND email_code='${mailCode}'`;
      conn.query(sql, (error, results) => {
        if (error) throw error
      });
      res.send({ code: 0, success: true, msg: '验证码正确' })
    } else {
      res.send({ code: 1, success: false, msg: '验证码错误' })
    }
  });

})

// 导出Excel

router.post('/student/exportExcel', (req, res) => {
  const { excelheader, excelData } = req.body;
  let r = [];
  let wch = [];
  let headerData = excelheader.map(item => {
    return item.label
  })
  headerData.forEach(item => {
    wch.push({wch: 30})
  });


  excelData.forEach((value, index) => {
    const keyIndex = Object.keys(value).indexOf('id');
    const keys = Object.keys(value); // 获取所有键名
    const keysCopy = [...keys]; // 创建原数组的副本，以避免直接修改
    if (keyIndex > -1) {
      keysCopy.splice(keyIndex, 1); // 移除索引为keyIndex的元素
    }
    let keysResult = keysCopy.map(item => {
      return value[item]
    })
    r.push(keysResult)
  })



  const Data = [
    {
      name: "我是sheet1", // 给第一个sheet指名字
      data: [
        headerData, // 表头
        ...r  //列表数据
      ],

    },
  ]
  // excel表格内容配置单元格宽度

  const sheetOptions = { '!cols': wch };

  // console.log(Data);
  // 解决打印[Array], [Array] 问题
  // console.log(JSON.stringify(Data,null,2));


  // excel表格内容数据格式
  // let excelData = [
  //   // 第一个sheet内容 第一个表
  //   {
  //     name: "我是sheet1", // 给第一个sheet指名字 
  //     data: [
  //       ["姓名", "年龄", "家乡", "备注"], // 第一行
  //       ["孙悟空", "500", "花果山", "人送外号斗战胜佛"], // 第二行
  //       ["猪八戒", "88", "高老庄", "天蓬元帅"], // 第三行
  //     ]
  //   },
  //   // 第二个sheet内容 第一个表
  //   {
  //     name: "我是sheet2", // 给第二个sheet指名字 
  //     data: [
  //       ["城市", "国家", "人口", "经济水平"], // 同上
  //       ["上海", "中国", "14亿", "越来越好"],
  //       ["伦敦", "英国", "7000万", "还行"],
  //       ["华盛顿", "美国", "3.4亿", "凑活"]
  //     ]
  //   }
  // ]

  //使用xlsx模块
  const buffer = xlsx.build(Data, { sheetOptions });

  // 第一种直接保存到服务器上
  // fs.writeFile("导出excel的名称.xlsx", buffer, function (err) {
  //   if (err) {
  //     console.log(err, "导出excel失败");
  //   } else {
  //     console.log("导出excel成功!");
  //   }
  // });

  // 第二种返回buffer blob流数据
  res.send(buffer)


})


//微信响应


module.exports = router; //暴露路由
